USE [Test_Events_Temp]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetEventIndicatorsByCompanyIds]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetEventIndicatorsByCompanyIds]
GO

CREATE PROCEDURE [dbo].[spGetEventIndicatorsByCompanyIds]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)

	SELECT [EventIndicatorId]
		  ,[EventIndicatorName]
		  ,[EventIndicatorSortKey]
		  ,[BaseEventIndicatorId]
		  ,[CompanyId]
		  ,[ControversyIndicatorId]
		  ,[ControversyIndicatorName]
		  ,[ControversyIndicatorNumber]
		  ,[ControversyIndicatorSortKey]
		  ,[ProvidesControversy]
		  ,[ControversySummary]
		  ,[AnswerCategoryId]
		  ,[AnswerCategoryText]
		  ,[AnswerCategoryScore]
		  ,[AnswerCategoryJustification]
		  ,[ImpactScore]
		  ,[ImpactText]
		  ,[ImpactCommentsId]
		  ,[RiskScore]
		  ,[RiskText]
		  ,[RiskCommentsId]
		  ,[PreparednessScore]
		  ,[PreparednessText]
		  ,[PreparednessCommentsId]
		  ,[OutlookScore]
		  ,[OutlookText]
		  ,[OutlookCommentsId]
		  ,[Timestamp]
	FROM [dbo].[PublishedEventIndicators] AS PublishedEventIndicators
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = PublishedEventIndicators.CompanyId
	 
END
GO


